<!DOCTYPE html>
<html lang="en"><head>
    <title>CZH-DEV BLOG</title>
    <meta content="text/html;charset=utf-8" http-equiv="Content-Type">
    <meta content="utf-8" http-equiv="encoding">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <meta name="format-detection" content="telephone=no" />
    <meta name="theme-color" content="#000084" />
    <link rel="icon" href="https://czh-dev.gitee.io/czh-blog.gitee.io//favicon.ico">
    <link rel="canonical" href="https://czh-dev.gitee.io/czh-blog.gitee.io/">
    
    
</head>
<body>
<nav class="navbar navbar-inverse navbar-fixed-top">
    <div class="navbar-inner">
        <div class="container">
            <button type="button" class="btn btn-navbar" data-toggle="collapse" data-target=".nav-collapse"></button>
            <a class="brand" href="https://czh-dev.gitee.io/czh-blog.gitee.io/">CZH-DEV BLOG</a>
            <div class="nav-collapse collapse">
                <ul class="nav">
                    
                    
                        
                            <li>
                                <a href="/czh-blog.gitee.io/about/">
                                    
                                    <span>About</span>
                                </a>
                            </li>
                        
                    
                        
                            <li>
                                <a href="/czh-blog.gitee.io/post/">
                                    
                                    <span>All posts</span>
                                </a>
                            </li>
                        
                    
                        
                            <li>
                                <a href="/czh-blog.gitee.io/ebook/">
                                    
                                    <span>Resource</span>
                                </a>
                            </li>
                        
                    
                </ul>
            </div>
        </div>
    </div>
</nav><div id="content" class="container">
<div style="display: flex;">
  <div class="row-fluid navmargin">
    <div class="page-header">
      <h1>一条查询SQL的查询流程 - Fri, Feb 10, 2023</h1>
    </div>
    <p class="lead"></p>
    <p>1、一条查询SQL执行流程图</p>
<p><img src="https://czh-pic.oss-cn-guangzhou.aliyuncs.com/202302110120489.jpg" alt="img"></p>
<p>2、查询SQL执行流程之发送SQL请求</p>
<p>（1）客户端按照Mysql通信协议将SQL发送到服务端，SQL到达服务端后，服务端会单起一个线程执行SQL。</p>
<p>（2）执行时Mysql首先判断SQL的前6个字符是否为select。并且语句中是否带有SQL_NO_CACHE关键字，如果没有则进入查询缓存。</p>
<p>3、查询SQL执行流程之查询缓存</p>
<p>查询缓存说白了就是一个哈希表，将执行过的语句及其结果以键值对的格式缓存到内存中。其中key是一个哈希值，由查询SQL、当前要查询的数据库、客户端协议版本等生成的，value就是查询结果。如果要绕过查询缓存，可以在SQL中加SQL_NO_CACHE字段，如：</p>
<pre tabindex="0"><code>SELECT SQL_NO_CACHE * FROM table
</code></pre><p>注：Mysql8.0版本开始取消查询缓存</p>
<p>4、查询SQL执行流程之解析器</p>
<p>解析器执行流程分为两个阶段，词法解析和语法解析</p>
<p>（1）首先对SQL词法进行分析，将SQL从左到右一个字符、一个字符地输入，然后根据构词规则识别单词。将会生成4个Token，如下所示：</p>
<p><img src="https://czh-pic.oss-cn-guangzhou.aliyuncs.com/202302110120146.jpg" alt="img"></p>
<p>（2）然后对SQL语法进行解析，判断客户端传入的SQL语句是否满足Mysql语法。此时会生成一颗语法树，如下所示：</p>
<p><img src="https://czh-pic.oss-cn-guangzhou.aliyuncs.com/202302110120179.jpg" alt="img"></p>
<p>如果语法不对，将会收到如下提示</p>
<pre tabindex="0"><code>You have an error in your SQL syntax
</code></pre><p>如果解析器顺利生成语法树，就会将SQL送发到预处理器</p>
<p>5、查询SQL执行流程之预处理器</p>
<p>预处理器主要做两件事情，查看SQL中列名是否正确和权限验证</p>
<p>（1）首先判断SQL语句中的列名是否存在于数据表中，再看看表名是否正确，如果不对，将返回如下错误提示</p>
<p>　Unknown column xxx in ‘where clause’</p>
<p>（2）预处理器对SQL进行权限验证，判断SQL是否有操作这个表的权限，若没有，则会返回如下错误信息</p>
<pre tabindex="0"><code>ERROR 1142 (42000): SELECT command denied to user &#39;root&#39;@&#39;localhost&#39; for table &#39;xxx&#39;
</code></pre><p>一切验证通过后将语法树传递给优化器</p>
<p>6、查询SQL执行流程之优化器</p>
<p>优化器的任务就是对SQL语句进行优化，达到最快的执行效果，优化器对SQL优化完成后会将SQL变成一个执行计划交给执行器</p>
<p>7、查询SQL执行流程之执行器</p>
<p>执行器就是根据执行计划来进行执行查询， 根据SQL的指令，逐条调用底层存储引擎，逐步执行。</p>
<p>MySQL定义了一系列抽象存储引擎API，以支持插件式存储引擎架构。Mysql实现了一个抽象接口层，叫做 handler(sql/handler.h)，其中定义了接口函数，比如：ha_open, ha_index_end, ha_create等等，存储引擎需要实现这些接口才能被系统使用。</p>

    <h4><a href="https://czh-dev.gitee.io/czh-blog.gitee.io/">Back to Home</a></h4>
  </div>

  <div class="span3 bs-docs-sidebar" style="position:fixed;right: 40px;top: 50px;">
    <h1>catalogue</h1>
    <ul class="nav nav-list bs-docs-sidenav">
      <div class="toc-div">
        <nav id="TableOfContents"></nav>
      </div>
    </ul>
  </div>

</div>
<script src="https://cdn.jsdelivr.net/npm/gumshoejs@5.1.2/dist/gumshoe.min.js"></script>
<script>
  var spy = new Gumshoe('#TableOfContents a', {
    nested: true,
    nestedClass: 'active'
  });
</script>
<style>
   
  #TableOfContents li,
  #TableOfContents ul {
    list-style-type: none;
  }

  #TableOfContents ul {
    padding-left: 0px;
  }

  #TableOfContents li>a {
    display: block;
    padding: 4px 20px;
    font-size: 95%;
    color: #000000;
  }

  #TableOfContents li>a:hover,
  #TableOfContents li>a:focus {
    padding-left: 19px;
    color: #3A6bA5;
    text-decoration: none;
    background-color: transparent;
    border-left: 1px solid #3A6bA5;
  }

  #TableOfContents li.active>a,
  #TableOfContents li.active>a:hover,
  #TableOfContents li.active>a:focus {
    padding-left: 18px;
    font-weight: bold;
    color: #3A6bA5;
    background-color: transparent;
    border-left: 2px solid #3A6bA5;
  }

   
  #TableOfContents li>ul {
    padding-bottom: 10px;
  }

  #TableOfContents li li>a {
    padding-top: 1px;
    padding-bottom: 1px;
    padding-left: 30px;
    font-size: 14px;
    font-weight: normal;
  }

  #TableOfContents li li>a:hover,
  #TableOfContents li li>a:focus {
    padding-left: 29px;
  }

  #TableOfContents li li.active>a,
  #TableOfContents li li.active>a:hover,
  #TableOfContents li li.active>a:focus {
    padding-left: 28px;
    font-weight: 500;
  }

  #TableOfContents .nav-link.active+ul {
    display: block;
  }

  #TableOfContents li>ul {
    display: none;
  }

  #TableOfContents li.active>ul {
    display: inherit;
  }

  .toc-div {
    position: -webkit-sticky;
     
    position: sticky;
     
    top: 20px;
  }
</style>


        </div><footer class="container">
    <hr class="soften">
    <p>
    <a href="https://space.bilibili.com/1799809923">Love eating fried pork ribs</a> | 

&copy; 
<a href="http://jmf-portfolio.netlify.com" target="_blank">
    JM Fergeau
</a>
<span id="thisyear">2023</span>

    | My site


        | Built on <a href="//gohugo.io" target="_blank">Hugo</a>

</p>
    <p class="text-center">
        <a href="https://facebook.com">Facebook</a> 
        <a href="https://twitter.com">Twitter</a> 
        <a href="https://linkedin.com">Linkedin</a> 
        <a href="https://github.com">GitHub</a> 
        <a href="https://gitlab.com">GitLab</a>
    </p>
</footer>

</body><link rel="stylesheet" href="/czh-blog.gitee.io/css/bootstrap.css">
<link rel="stylesheet" href="/czh-blog.gitee.io/css/bootstrap-responsive.css">
<link rel="stylesheet" href="/czh-blog.gitee.io/css/style.css">

<script src="/czh-blog.gitee.io/js/jquery.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-386.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-transition.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-alert.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-modal.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-dropdown.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-scrollspy.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-tab.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-tooltip.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-popover.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-button.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-collapse.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-carousel.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-typeahead.js"></script>
<script src="/czh-blog.gitee.io/js/bootstrap-affix.js"></script>
<script>
    _386 = { 
        fastLoad: false ,
        onePass: false , 
        speedFactor: 1 
    };

    
    function ThisYear() {
        document.getElementById('thisyear').innerHTML = new Date().getFullYear();
    };
</script>
</html>
